package com.example.luke.shengdangxiaoattendance.Util;

import android.Manifest;
import android.app.Activity;
import android.content.Context;
import android.content.pm.PackageManager;
import android.hardware.usb.UsbDevice;
import android.hardware.usb.UsbManager;
import android.os.Environment;
import android.support.v4.app.ActivityCompat;
import android.util.Log;

import com.example.luke.shengdangxiaoattendance.Model.ClassBean;
import com.example.luke.shengdangxiaoattendance.Model.Course;
import com.example.luke.shengdangxiaoattendance.Model.Student;

//import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFRow;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.openxml4j.opc.OPCPackage;
//import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//import org.apache.poi.ss.usermodel.Cell;
//import org.apache.poi.ss.usermodel.CellValue;
//import org.apache.poi.ss.usermodel.Comment;
//import org.apache.poi.ss.usermodel.FormulaEvaluator;
//import org.apache.poi.ss.usermodel.Row;
//import org.apache.poi.ss.usermodel.Sheet;
//import org.apache.poi.ss.usermodel.Workbook;
//import org.apache.poi.ss.usermodel.WorkbookFactory;
//import org.apache.poi.xssf.usermodel.XSSFSheet;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * Created by luke on 23/05/2017.
 */

public class ExcelUtils {


public  static void getFileList(Context context){

    //初始化
//    Student.clearAllStudent(context);//先清空学生列表
//    Course.clearAllCourse(context);

    File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"省党校");

    File fileList[]=file.listFiles();

    for (File temp:fileList){



        if (temp.getName().contains("课程表")){


            try {

                initCourseClass(context,temp.getAbsolutePath());

            }catch (Exception e){

            }

        }

//        if (!temp.getName().contains("课程表")){
//
//
//            try {
//
//
//                initStudentList(context,temp.getAbsolutePath());
//
//            }catch (Exception e){
//
//            }
//
//        }
    }
}

    public  static void initStudentList(Context context){

        //初始化
        //Student.clearAllStudent(context);//先清空学生列表

        File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"省党校");

        File fileList[]=file.listFiles();

        for (File temp:fileList){


            if (!temp.getName().contains("课程表")) {

                initStudentList(context,temp.getAbsolutePath());

            }

        }
    }



    public  static ArrayList<ClassBean> getClassList(Context context){


        File file=new File(Environment.getExternalStorageDirectory().toString()+"/"+"省党校");

        File fileList[]=file.listFiles();
        ArrayList<ClassBean>classBeanArrayList=new ArrayList<>();

        for (File temp:fileList){


            if (temp.getName().contains("教室名称")) {

                GlobalFunction.printTextByTag("123");
           classBeanArrayList=  analyzeClassExcelFile   (temp.getAbsolutePath());

            }

        }

        return classBeanArrayList;

    }

    private static ArrayList<ClassBean> analyzeClassExcelFile(String classFilePath){

        jxl.Workbook readwb = null;
        ArrayList<ClassBean>classBeanArrayList=new ArrayList<>();


        try

        {

            //构建Workbook对象, 只读Workbook对象

            //直接从本地文件创建Workbook

            InputStream instream = new FileInputStream(classFilePath);

            readwb = Workbook.getWorkbook(instream);


            //Sheet的下标是从0开始

            //获取第一张Sheet表

            Sheet readsheet = readwb.getSheet(0);

            //获取Sheet表中所包含的总列数

            int rsColumns = readsheet.getColumns();

            //获取Sheet表中所包含的总行数

            int rsRows = readsheet.getRows();

            //获取指定单元格的对象引用

            for (int i = 1; i < rsRows; i++)

            {
                ClassBean course = new ClassBean();


                for (int j = 0; j < rsColumns; j++)

                {

                    Cell cell = readsheet.getCell(j, i);

                    String val = cell.getContents();

                    switch (j) {
                        case 1:
                            course.className = val;

                            break;
                        case 2:
                            course.classCode = val;

                            break;


                    }
                }

                classBeanArrayList.add(course);
            }

        }catch (Exception e){

            e.printStackTrace();
        }
        return classBeanArrayList;

   }

  private  static  void initCourseClass(Context context,String classFilePath){

      GlobalFunction.printTextByTag("init course");
      jxl.Workbook readwb = null;
      Course.getInstance(context).courseArrayList=new ArrayList<>();

      try

      {

          //构建Workbook对象, 只读Workbook对象

          //直接从本地文件创建Workbook

          InputStream instream = new FileInputStream(classFilePath);

          readwb = Workbook.getWorkbook(instream);


          //Sheet的下标是从0开始

          //获取第一张Sheet表

          Sheet readsheet = readwb.getSheet(0);

          //获取Sheet表中所包含的总列数

          int rsColumns = readsheet.getColumns();

          //获取Sheet表中所包含的总行数

          int rsRows = readsheet.getRows();

          //获取指定单元格的对象引用

          for (int i = 1; i < rsRows; i++)

          {
              Course course=new Course();



              for (int j = 0; j < rsColumns; j++)

              {

              Cell cell = readsheet.getCell(j, i);

              String val=cell.getContents();

             switch (j){
                case 1:
                    course.Time=val;

                    break;
                case 2:
                    course.name1=val;

                    break;
                case 3:
                    course.name2=val;

                    break;
                case 4:
                    course.teacher=val;

                    break;
                case 5:
                    course.className=val;

                    break;

            }
              }


     Course.getInstance(context).storeToDB(context,course);



    }


      }catch (Exception e){
          GlobalFunction.printTextByTag(e.getMessage());
      }

}

    private  static  void initStudentList(Context context,String classFilePath){
        jxl.Workbook readwb = null;

        try

        {

            //构建Workbook对象, 只读Workbook对象

            //直接从本地文件创建Workbook

            InputStream instream = new FileInputStream(classFilePath);

            readwb = Workbook.getWorkbook(instream);


            //Sheet的下标是从0开始

            //获取第一张Sheet表

            Sheet readsheet = readwb.getSheet(0);

            //获取Sheet表中所包含的总列数

            int rsColumns = readsheet.getColumns();

            //获取Sheet表中所包含的总行数

            int rsRows = readsheet.getRows();

            //获取指定单元格的对象引用

            for (int i = 1; i < rsRows; i++)

            {

                Student course=new Student();

                for (int j = 0; j < rsColumns; j++)

                {

                    Cell cell = readsheet.getCell(j, i);
                    String val = cell.getContents();


                    switch (j) {
                        case 1:
                            course.className = val;

                            break;
                        case 2:
                            course.name = val;

                            break;
                        case 3:
                            course.employeeID = val;

                            break;
                        case 4:
                            course.issueUnit = val;

                            break;
                        case 5:
                            course.gender = val;

                            break;
                        case 6:
                            course.age = val;

                            break;
                        case 7:
                            course.culture = val;

                            break;
                        case 8:
                            course.studentID = val;

                            break;

                    }

                }
        GlobalFunction.printTextByTag(course.className+""+course.name);
                Student.saveStudent(context,course);
           //     System.out.println();

            }


        }catch (Exception e){
            GlobalFunction.printTextByTag(e.getMessage());
        }

//        try {
//
//           Workbook book = WorkbookFactory.create(new File(classFilePath));
//
//            Sheet sheet=book.getSheetAt(0);
//            FormulaEvaluator formulaEvaluator=book.getCreationHelper().createFormulaEvaluator();
//
//            int rows=sheet.getPhysicalNumberOfRows();
//
//            for (int i=1;i<rows;i++){
//
//                Row row=sheet.getRow(i);
//
//
//                Student course= new Student();
//                int cellCount=row.getPhysicalNumberOfCells();
//
//                for (int c=0;c<cellCount;c++){
//                    String val=  getCellAsString(row,c,formulaEvaluator);
//
//                    switch (c){
//                        case 0:
//                            course.className=val;
//
//                            break;
//                        case 1:
//                            course.name=val;
//
//                            break;
//                        case 2:
//                            course.employeeID=val;
//
//                            break;
//                        case 3:
//                            course.issueUnit=val;
//
//                            break;
//                        case 4:
//                            course.gender=val;
//
//                            break;
//                        case 5:
//                            course.age=val;
//
//                            break;
//                        case 6:
//                            course.culture=val;
//
//                            break;
//                        case 7:
//                            course.studentID=val;
//
//                            break;
//
//                    }
//
//                }
//
//        Student.saveStudent(context,course);
//           //    GlobalFunction.printTextByTag(course.className+":"+course.name+":"+course.employeeID+":"+course.issueUnit+":"+course.studentID);
//
//
//            }
//
//
//        }catch(Exception e){
//
//            GlobalFunction.printTextByTag(e.getMessage());
//
//        }

    }

    public static void jxReadEcel(String filePath) throws BiffException,IOException {
        jxl.Workbook readwb = null;

        try

        {

            //构建Workbook对象, 只读Workbook对象

            //直接从本地文件创建Workbook

            InputStream instream = new FileInputStream(filePath);

            readwb = Workbook.getWorkbook(instream);


            //Sheet的下标是从0开始

            //获取第一张Sheet表

            Sheet readsheet = readwb.getSheet(0);

            //获取Sheet表中所包含的总列数

            int rsColumns = readsheet.getColumns();

            //获取Sheet表中所包含的总行数

            int rsRows = readsheet.getRows();

            //获取指定单元格的对象引用

            for (int i = 0; i < rsRows; i++)

            {

                for (int j = 0; j < rsColumns; j++)

                {

                    Cell cell = readsheet.getCell(j, i);

                    GlobalFunction.printTextByTag(cell.getContents());
                    //  System.out.print(cell.getContents() + " ");

                }

                System.out.println();

            }


        }catch (Exception e){
            GlobalFunction.printTextByTag(e.getMessage());
        }
    }
    private   static  void readExcel(String filePath) {

//        try {
//
//
//        XSSFWorkbook book = new XSSFWorkbook(filePath);
//
//         XSSFSheet sheet=book.getSheetAt(0);
//         FormulaEvaluator formulaEvaluator=book.getCreationHelper().createFormulaEvaluator();
//
//        int rows=sheet.getPhysicalNumberOfRows();
//
//          for (int i=1;i<rows;i++){
//
//              Row row=sheet.getRow(i);
//
//              int cellCount=row.getPhysicalNumberOfCells();
//
//              for (int c=0;c<cellCount;c++){
//
//
//                 getCellAsString(row,c,formulaEvaluator);
//              }
//          }
//
//
//            }catch(Exception e){
//
//                GlobalFunction.printTextByTag(e.getMessage());
//
//            }

     }


//    private static String getCellAsString(Row row,int c,FormulaEvaluator formulaEvaluator){
//        String value="";
//
////        try {
////            Cell cell=row.getCell(c);
////
////            if (cell!=null){
////
////                CellValue cellValue=formulaEvaluator.evaluate(cell);
////
////                switch (cellValue.getCellType()){
////
////                    case Cell.CELL_TYPE_STRING:
////
////                        value=cellValue.getStringValue();
////
////                        break;
////
////                }
////
////            }
////
////
////
////
////        }catch (Exception e){
////
////            GlobalFunction.printTextByTag(e.getMessage());
////
////        }
//
//        return value;
//    }







    // Storage Permissions
    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {
            Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE,
            Manifest.permission.READ_PHONE_STATE,Manifest.permission.NFC
    };

    /**
     * Checks if the app has permission to write to device storage
     *
     * If the app does not has permission then the user will be prompted to grant permissions
     *
     * @param activity
     */
    public static boolean verifyStoragePermissions(Activity activity) {
        // Check if we have write permission
        int permission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.WRITE_EXTERNAL_STORAGE);

        if (permission != PackageManager.PERMISSION_GRANTED) {
            // We don't have permission so prompt the user
            ActivityCompat.requestPermissions(
                    activity,
                    PERMISSIONS_STORAGE,
                    REQUEST_EXTERNAL_STORAGE
            );



            return false;

        }else
            return true;


    }

    public  static File  creatDir(Activity context,String fileName){
        verifyStoragePermissions(context);



        File dir = new File(Environment.getExternalStorageDirectory().toString(),fileName);

        try{
            if (dir.exists()){

                return dir;

            }else {

                if(dir.mkdir()) {

                    //  GlobalFunction.ToastAlert(context,"创建文件夹成功");
                GlobalFunction.printTextByTag("Directory created");


                } else {

                    GlobalFunction.printTextByTag("Directory is not created");
                    //   GlobalFunction.ToastAlert(context,"创建文件夹失败");

                    return  null;


                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }



        return  dir;


    }

}
